"""
@name : make_map
@author : zhangjian
@projectname: tlbb
"""
import pandas as pd
from sqlalchemy import create_engine
from config import DBURL
import matplotlib
import matplotlib.pyplot as plt
import numpy as np

#连接数据库
conn = create_engine(DBURL)
#获取china 2021年对所有国家的交易数据
df = pd.read_sql("select * from union_table where Reporter='China' and Year='2020';", con=conn)
#与中国进行大米交易前三的国家数据 Partner得到的国家就成为这个dataframe 的index了
result = df.groupby('Partner').sum().sort_values(by="Netweight (kg)", ascending=False)[1:4]
#sql语句 直接查询
#select Partner, sum(Qty) as nw from union_table where Reporter='China' and Year='2021' group by Partner order by nw desc limit 5;
#把前三的国家 交易数据放入result_df
result_df = df[df['Partner'].isin(result.index) ]
#把Partner作为result_df索引
result_df.set_index(['Partner'], inplace=True)
#筛选交易类型和交易量
result_df = result_df[['Trade Flow','Netweight (kg)']]
s1 = result_df[result_df["Trade Flow"] == "Export"]['Netweight (kg)']
s2 = result_df[result_df["Trade Flow"] == "Import"]['Netweight (kg)']
rdf = pd.concat([s1,s2], axis=1)
rdf.columns = ["export","import"]
# 画图
rdf.plot.bar()
# data.plot.bar(color="b")
plt.xticks(rotation=360)
# fontproperties='simhei'，让python画图支持中文格式
plt.title(f"2020中国大米进出口总量前三排名",fontproperties='simhei')
plt.savefig('Chian2022.png')
# plt.legend()
plt.show()

#另一个绘图方法
# def make_map(df):
#     '''传入一个df，按数据绘图'''
#
#     partnerdf = df.groupby('Partner').sum()
#     top3df = partnerdf.sort_values(by="Qty", ascending=False)[1:4]
#     y1 = []
#     y2 = []
#     for i in top3df.index:
#         importdf = df[(df['Partner'] == i) & (df['Trade Flow'] == 'Import')]
#         exportdf = df[(df['Partner'] == i) & (df['Trade Flow'] == 'Export')]
#         importdata = importdf['Qty'].sum()
#         exportdata = exportdf['Qty'].sum()
#         y1.append(importdata)
#         y2.append(exportdata)
#
#     # 设置中文字体
#     matplotlib.rcParams["font.sans-serif"] = ["SimHei"]
#     matplotlib.rcParams["axes.unicode_minus"] = False
#     # 构建数据
#     x = np.arange(3)
#     bar_width = 0.35
#     tick_label = list(top3df.index)
#     # 绘制柱状图
#     plt.figure(figsize=(4, 4))
#
#     plt.bar(x, y1, bar_width, align="center", color='r', tick_label=tick_label, label='进口')
#     plt.bar(x + bar_width, y2, bar_width, align="center", color='b', tick_label=tick_label, label='出口')
#
#     plt.xlabel("数据类别")
#     plt.ylabel("数量")
#     # 设置x轴刻度显示位置
#     plt.xticks(x + bar_width / 2, tick_label)
#
#     plt.legend(loc='upper right')
#     plt.savefig("result.png")
#     plt.show()
